﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;  // for exe path of application
using MySql.Data.MySqlClient;// for connection with database

namespace Prop
{
    class DataHelper
    {
        private MySqlConnection connection;


        public DataHelper()
        {
           
            String connectionInfo = "server=athena01.fhict.local;" +
                             "database=dbi269964;" + // your database name
                             "user id=dbi269964;" + // your user id
                             "password=ztauJSorfR;" + // your password
                             "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);

        }

        public ShopVisitor getVisitor(int visiterId) // Return Account information from a certain visitor with the perameter visitor id 
        {
            String visId = Convert.ToString(visiterId);
            String sql = "SELECT v.Visitor_id,v.LastName,v.FirstName,e.MoneyDeposited FROM visitor v JOIN event_account e ON v.Visitor_Id=e.Visitor_id WHERE v.visitor_Id='" + visiterId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<ShopVisitor> temp;
            temp = new List<ShopVisitor>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string name1, name2;
                double balance;
                int no;

                while (reader.Read())
                {
                    no = Convert.ToInt32(reader["Visitor_id"]);
                    name1 = Convert.ToString(reader["LastName"]);
                    name2 = Convert.ToString(reader["FirstName"]);
                    balance = Convert.ToDouble(reader["MoneyDeposited"]);

                    temp.Add(new ShopVisitor(no, name1, name2, balance));
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp[0];
        }
        public int updateNoInStock(int productId, int stock)
        {
            String proId = Convert.ToString(productId);
            String sql = "UPDATE PRODUCT_HIRED SET ProductsInStock=" + stock + " WHERE ProductId='" + proId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        public int addOrder(long orderNo, double price, int shopNo, int visitorId)
        {

            String receiptNo = Convert.ToString(orderNo);
            String guestId = Convert.ToString(visitorId);
            String sql = "INSERT INTO FOOD_RECEIPTVALUES(" + receiptNo + "," + price + "," + shopNo + "," + guestId + ")";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();


                int nrOfRecordsChanged = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }
        public int addOrderLine(long orderNo,long orderLine, int numProds, int prodId)
        {




            //String Order = Convert.ToString(order);
            String product = Convert.ToString(prodId);

           
            String sql = "INSERT INTO RECEIPT_LINE VALUES("+orderNo+","+orderLine+ "," + numProds + ",'" + product + "')";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();

                int nrOfRecordsChanged = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }

        }
        public int updateBalance(int visitorId, double balance)
        {
            String Id = Convert.ToString(visitorId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneyDeposited=" + balance + " WHERE Visitor_id='" + Id + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }

        public List<Item> loadAllProducts()// Returns A list of all products.
        {
            String sql = "SELECT * FROM product";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<Item> temp;
            temp = new List<Item>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                int productId;
                String productName;
                int unitPrice;
                int noInStock;
                int food;
                int alc;
                int veg;
                bool isVeg;
                bool isAlc;
                bool isFood;
                while (reader.Read())
                {
                    productId = Convert.ToInt32(reader["ProductId"]);
                    productName = Convert.ToString(reader["ProductName"]);
                    unitPrice = Convert.ToInt32(reader["UnitPrice"]);
                    noInStock = Convert.ToInt32(reader["ProductsInStock"]);
                    alc = Convert.ToInt32(reader["Alcholic"]);
                    veg = Convert.ToInt32(reader["Vegatarian"]);
                    food = Convert.ToInt32(reader["food"]);
                    if (alc == 1)
                    {
                        isAlc = true;
                    }
                    else
                    {
                        isAlc = false;
                    }
                    if (veg == 1)
                    {
                        isVeg = true;
                    }
                    else
                    {
                        isVeg = false;
                    }
                    if (food == 1)
                    {
                        isFood = true;
                    }
                    else
                    {
                        isFood = false;
                    }
                    if (isFood)
                    {
                        temp.Add(new Food(productId, productName, unitPrice, noInStock, isVeg));
                    }
                    else
                    {
                        temp.Add(new Drink(productId, productName, unitPrice, noInStock, isAlc));
                    }
                }
            }
            catch (MySqlException err)
            {
                MessageBox.Show("Error: " + err.ToString());
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }

        public int updateMoneySpent(int clientId, double spent)
        {
            String visitorId = Convert.ToString(clientId);
            String sql = "UPDATE EVENT_ACCOUNT SET MoneySpent=MoneySpent+" + spent + " WHERE Visitor_id='" + visitorId + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1; //which means the try-block was not executed succesfully, so  . . .
            }
            finally
            {
                connection.Close();
            }
        }
        public int maxReceiptNo()// returns the number of times someone has exited the whole event.
        {
            String sql = "SELECT MAX(ReceiptNo) FROM receipt_line";
            MySqlCommand command = new MySqlCommand(sql, connection);
            int number = 0;
            try
            {
                connection.Open();
                number = Convert.ToInt32(command.ExecuteScalar());
                return number;
            }
            catch
            {
                return -1;

            }
            finally
            {
                connection.Close();
            }
        }
       
    }
}


    

